<?php
    include_once '../../../../controlador/php/conexion/Conexion.php';
    $con = new Conexion();
    
    $f1 = $_REQUEST['capass2'];
    $f2 = $_REQUEST['capass22'];
    
    $fecha1 = date('Y-m-d',strtotime($f1));
    $fecha2 = date('Y-m-d',strtotime($f2));
    
    $estadis = "DECLARE @fecha1 date;
                DECLARE @fecha2 date;

                SET @fecha1 = '".$fecha1."';
                SET @fecha2 = '".$fecha2."';
                    
                select PACNUMDOC,lt2.adnIngreso,GENPACIEN.PACPRINOM + ' ' + GENPACIEN.PACSEGNOM + ' ' + GENPACIEN.PACPRIAPE + ' ' + GENPACIEN.PACSEGAPE as nombres ,  
                    CASE WHEN cro.cant IS NULL THEN '0' ELSE cro.cant END AS cro,
                    CASE WHEN st.cant IS NULL THEN '0' ELSE st.cant END AS st,
                    CASE WHEN grff.cant IS NULL THEN '0' ELSE grff.cant END AS grf,
                    CASE WHEN grpl.cant IS NULL THEN '0' ELSE grpl.cant END AS grpl,
                    CASE WHEN pqa.cant IS NULL THEN '0' ELSE pqa.cant END AS pqa,
                    CASE WHEN pfc.cant IS NULL THEN '0' ELSE pfc.cant END AS pfc,
                    CASE WHEN pqm.cant IS NULL THEN '0' ELSE pqm.cant END AS pqm,
                    CASE WHEN com.cant IS NULL THEN '0' ELSE com.cant END AS com,
                    CASE WHEN inc.cant IS NULL THEN '0' ELSE inc.cant END AS inc,
                    CASE WHEN rai.canti IS NULL THEN '0' ELSE rai.canti END AS rai,
                    CASE WHEN noa.cant IS NULL THEN '0' ELSE noa.cant END AS noa,
                    CASE WHEN ser.cant IS NULL THEN '0' ELSE ser.cant END AS urgencias,
                    CASE WHEN consultaa.cant IS NULL THEN '0' ELSE consultaa.cant END AS consultaa,
                    CASE WHEN recuperacion.cant IS NULL THEN '0' ELSE recuperacion.cant END AS recuperacion,
                    CASE WHEN cuidadoi.cant IS NULL THEN '0' ELSE cuidadoi.cant END AS cuidadoi,
                    CASE WHEN unidadrn.cant IS NULL THEN '0' ELSE unidadrn.cant END AS unidadrn,
                    CASE WHEN cirugiag.cant IS NULL THEN '0' ELSE cirugiag.cant END AS cirugiag,
                    CASE WHEN unidado.cant IS NULL THEN '0' ELSE unidado.cant END AS unidado,
                    CASE WHEN h2s.cant IS NULL THEN '0' ELSE h2s.cant END AS h2s,
                    CASE WHEN h3s.cant IS NULL THEN '0' ELSE h3s.cant END AS h3s,
                    CASE WHEN h4s.cant IS NULL THEN '0' ELSE h4s.cant END AS h4s,
                    CASE WHEN h4n.cant IS NULL THEN '0' ELSE h4n.cant END AS h4n,
                    CASE WHEN h5s.cant IS NULL THEN '0' ELSE h5s.cant END AS h5s,
                    CASE WHEN h5n.cant IS NULL THEN '0' ELSE h5n.cant END AS h5n,
                    CASE WHEN unidadci.cant IS NULL THEN '0' ELSE unidadci.cant END AS unidadci,
                    CASE WHEN oncologos.cant IS NULL THEN '0' ELSE oncologos.cant END AS oncologos,
                    CASE WHEN consultae.cant IS NULL THEN '0' ELSE consultae.cant END AS consultae,
                    CASE WHEN otro.cant IS NULL THEN '0' ELSE otro.cant END AS otro,
                    CASE WHEN psc.cant IS NULL THEN '0' ELSE psc.cant END AS psc,
                    CASE WHEN ud.cant IS NULL THEN '0' ELSE ud.cant END AS ud,
                    ser.ser_oid as u,consultaa.ser_oid as ca,recuperacion.ser_oid as r,cuidadoi.ser_oid as ci,unidadrn.ser_oid as urn,cirugiag.ser_oid as cg,
                    unidado.ser_oid as uo,h2s.ser_oid as ho2s,h3s.ser_oid as ho3s,h4s.ser_oid as ho4s,h4n.ser_oid as ho4n,h5s.ser_oid as ho5s,h5n.ser_oid as ho5n,unidadci.ser_oid as uci,
                    oncologos.ser_oid as onc,consultae.ser_oid as ce,otro.ser_oid as otr
                    from lTransfunc as lt2 left outer join ".$con->getReal()."..GENPACIEN on lt2.genPacien=".$con->getReal()."..GENPACIEN.OID 
                        outer apply (select lt1.ltr_componente as componente, COUNT(*) as cant
                                                from lTransfunc as lt1 where lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_componente='st' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_componente) as st
                        outer apply (select lt1.ltr_componente as componente, COUNT(*) as cant
                                                from lTransfunc as lt1 where lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_componente='cro' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_componente) as cro
                        outer apply (select lt1.ltr_componente as componente, COUNT(*) as cant
                                                from lTransfunc as lt1 where lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_componente='grf' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_componente) as grff
                        outer apply (select lt1.ltr_componente as componente, COUNT(*) as cant
                                                from lTransfunc as lt1 where  lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_componente='grpl' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_componente) as grpl
                        outer apply (select lt1.ltr_componente as componente, COUNT(*) as cant
                                                from lTransfunc as lt1 where lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_componente='pqa' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_componente) as pqa
                        outer apply (select lt1.ltr_componente as componente, COUNT(*) as cant
                                                from lTransfunc as lt1 where lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_componente='pfc' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_componente) as pfc
                        outer apply (select lt1.ltr_componente as componente, COUNT(*) as cant
                                                from lTransfunc as lt1 where lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_componente='pqm' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_componente) as pqm
                        outer apply (select lt1.ltr_interpret as interpretacion, COUNT(*) as cant
                                                from lTransfunc as lt1 where lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_interpret ='com' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_interpret) as com
                        outer apply (select lt1.ltr_interpret as interpretacion, COUNT(*) as cant
                                                from lTransfunc as lt1 where lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_interpret ='inc' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_interpret) as inc
                        outer apply (select lt1.ltr_interpret as interpretacion, COUNT(*) as cant
                                                from lTransfunc as lt1 where lt1.ltr_fecha between @fecha1 and @fecha2 and lt1.ltr_interpret ='noa' 
                                                and lt1.adnIngreso=lt2.adnIngreso group by lt1.ltr_interpret) as noa
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =1
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as ser
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =2
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as consultaa
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =3
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as recuperacion
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =4
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as cuidadoi
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =5
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as unidadrn
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =6
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as cirugiag
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =7
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as unidado
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =8
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as h2s
                         outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =18
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as h3s
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =9
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as h4s  
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =10
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as h4n
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =11
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as h5s
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =12
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as h5n  
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =13
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as unidadci
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =15
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as oncologos
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =17
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as consultae
                        outer apply (select serviciosL.ser_oid,serviciosL.ser_descripcion as servicio, COUNT(*) as cant
                                                from lTransfunc as lt1 left outer join serviciosL on lt1.servicio=serviciosL.ser_oid
                                                where lt1.ltr_fecha between @fecha1 and @fecha2 and serviciosL.ser_oid =16
                                                and lt1.adnIngreso=lt2.adnIngreso group by serviciosL.ser_oid,serviciosL.ser_descripcion) as otro      
                        outer apply (select SUM(na.cant + noa.cant)as cant 
                                                 from lTransfunc as ltn
                                                        outer apply (select COUNT(*) as cant
                                                                                 from lTransfunc as ltna where ltr_fecha between @fecha1 and @fecha2 and ltna.ltr_pruebC = 'na' 
                                                                                 and ltn.ltr_oid=ltna.ltr_oid ) as na
                                                        outer apply (select COUNT(*) as cant
                                                                                 from lTransfunc as ltna where ltr_fecha between @fecha1 and @fecha2 and ltna.ltr_sinpc = 1
                                                                                 and ltn.ltr_oid=ltna.ltr_oid ) as noa
                                                        where ltr_fecha between @fecha1 and @fecha2 and ltn.adnIngreso=lt2.adnIngreso) as psc
                        outer apply(select SUM(ne.cant + est.cant)as cant 
                                                from lTransfunc as ltn
                                                outer apply (select COUNT(*) as cant
                                                                         from lTransfunc as ltna where ltr_fecha between @fecha1 and @fecha2 and ltna.ltr_estado = 1 
                                                                         and ltn.ltr_oid=ltna.ltr_oid ) as ne
                                                outer apply (select COUNT(*) as cant
                                                                         from lTransfunc as ltna where ltr_fecha between @fecha1 and @fecha2 and ltna.ltr_estado = 2 
                                                                         and ltn.ltr_oid=ltna.ltr_oid ) as est
                                                        where ltr_fecha between @fecha1 and @fecha2 and ltn.adnIngreso=lt2.adnIngreso)as ud
                        outer apply(select SUM(ne.cant + po.cant)as canti  
                                                 from lTransfunc as ltn
                                                        outer apply (select COUNT(*) as cant
                                                                                 from lTransfunc as ltna where ltr_fecha between @fecha1 and @fecha2 and ltna.ltr_rai1  like '%negativo%' 
                                                                                 and ltn.ltr_oid=ltna.ltr_oid) as ne
                                                        outer apply (select COUNT(*) as cant
                                                                                 from lTransfunc as ltna where ltr_fecha between @fecha1 and @fecha2 and ltna.ltr_rai1 like '%positivo%' 
                                                                                 and ltn.ltr_oid=ltna.ltr_oid) as po
                                                        where ltr_fecha between @fecha1 and @fecha2 and ltn.adnIngreso=lt2.adnIngreso)as rai
                where ltr_fecha between @fecha1 and @fecha2


                group by  PACNUMDOC,lt2.adnIngreso,GENPACIEN.PACPRINOM,GENPACIEN.PACSEGNOM,GENPACIEN.PACPRIAPE,GENPACIEN.PACSEGAPE,cro.componente, cro.cant, grpl.componente, grpl.cant, pqa.componente,pqa.cant,pfc.componente,pfc.cant,pqm.componente,pqm.cant,
                                  com.interpretacion,com.cant,inc.interpretacion,inc.cant,noa.interpretacion,noa.cant,ser.cant,consultaa.cant,recuperacion.cant,cuidadoi.cant,
                                  unidadrn.cant,cirugiag.cant,unidado.cant,h2s.cant,h3s.cant,h4s.cant,h4n.cant,h5s.cant,h5n.cant,h5n.cant,unidadci.cant,oncologos.cant,psc.cant,consultae.cant,otro.cant,ud.cant,
                                  ser.ser_oid,consultaa.ser_oid,recuperacion.ser_oid,cuidadoi.ser_oid,unidadrn.ser_oid,cirugiag.ser_oid,
                          unidado.ser_oid,h2s.ser_oid,h3s.ser_oid,h4s.ser_oid,h4n.ser_oid,h5s.ser_oid,h5n.ser_oid,unidadci.ser_oid,
                          oncologos.ser_oid,consultae.ser_oid,otro.ser_oid,st.cant,rai.canti,grff.cant";
    $lista = $con->query($estadis);
    
    while($lstLista = mssql_fetch_array($lista)){
        $respuesta[] = array_map('utf8_encode',$lstLista);
    }
    
    echo json_encode($respuesta);
    
    $con->cerrarConexion();
?>
